package org.anyline.data.jdbc.adapter.init;

import org.anyline.data.param.ConfigStore;
import org.anyline.data.run.Run;
import org.anyline.data.runtime.DataRuntime;
import org.anyline.entity.DataRow;
import org.anyline.entity.DataSet;
import org.anyline.entity.generator.PrimaryGenerator;
import org.anyline.metadata.Column;
import org.anyline.metadata.Sequence;
import org.anyline.proxy.EntityAdapterProxy;
import org.anyline.util.BeanUtil;
import org.anyline.util.SQLUtil;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.*;

public abstract class OracleGenusAdapter extends SQLAdapter {

    /**
     * 批量插入
     * 有序列时 只支持插入同一张表
     * INSERT INTO CRM_USER(ID, NAME)
     *  SELECT gloable_seq.nextval  AS ID  , M.* FROM (
     * 		SELECT  'A1' AS NM FROM  DUAL
     * 		UNION ALL SELECT    'A2' FROM DUAL
     * 		UNION ALL SELECT    'A3' FROM DUAL
     * )
     * //重复覆盖或略过
     * MMERGE INTO CRM_USER M
     *         USING (
     *                 SELECT
     *                     I.ID AS ID, I.CODE AS CODE, I.NAME AS NAME
     *                 FROM(
     *                     SELECT 12 AS ID,1 AS CODE,12 AS NAME   FROM DUAL
     *                     UNION ALL
     *                     SELECT 22 AS ID,1 AS CODE,22 AS NAME  FROM DUAL
     *                 ) I
     *         ) D ON (D.ID=M.ID)
     *         WHEN NOT MATCHED THEN
     *             INSERT(M.ID,M.CODE,M.NAME) VALUES(D.ID,D.CODE,D.NAME)
     *         WHEN MATCHED THEN
     *             UPDATE SET M.CODE=D.CODE, M.NAME = D.NAME
     * @param runtime 运行环境主要包含驱动适配器 数据源或客户端
     * @param run 最终待执行的命令和参数(如果是JDBC环境就是SQL)
     * @param dest dest
     * @param columns columns
     * @param configs ConfigStore
     */
    @Override
    public void fillInsertContent(DataRuntime runtime, Run run, String dest, DataSet set, ConfigStore configs, LinkedHashMap<String, Column> columns){
        if(null == set || set.isEmpty()){
            return;
        }
        StringBuilder builder = run.getBuilder();
        DataRow first = set.getRow(0);
        Map<String, Sequence> sequens = new HashMap<>();
        for(Column column:columns.values()){
            String key = column.getName();
            Object value = first.getStringNvl(key);
            if(null != value) {
                if(value instanceof String) {
                    String str = (String) value;
                    if (str.toUpperCase().contains(".NEXTVAL")) {
                        if (str.startsWith("${") && str.endsWith("}")) {
                            str = str.substring(2, str.length() - 1);
                        }
                        sequens.put(key, new Sequence(str));
                    }
                }else if(value instanceof Sequence){
                    Sequence sequence = (Sequence) value;
                    if (sequence.isFetchValueBeforeInsert()) {
                        createPrimaryValue(runtime, set, sequence);
                    } else {
                        sequens.put(key, sequence);
                    }
                }
            }
        }

        PrimaryGenerator generator = checkPrimaryGenerator(type(), dest.replace(getDelimiterFr(), "").replace(getDelimiterTo(), ""));
        LinkedHashMap<String, Column> pks = null;
        if(null != generator) {
            pks = first.getPrimaryColumns();
            columns.putAll(pks);
        }

        Boolean override = null;
        if(null != configs){
            override = configs.override();
        }
        String select = insertsSelect(runtime, run, dest, set, configs, columns, sequens, generator, pks);
        if(null == override) {
            //正常插入
            builder.append("INSERT INTO ");
            SQLUtil.delimiter(builder, dest, getDelimiterFr(), getDelimiterTo()).append(" (");
            builder.append(concat("",",", Column.names(columns)));
            builder.append(") \n");
            builder.append(select);
        }else{
            //重复时 是否覆盖
           merge(builder, dest, configs, select, columns, pks);
        }
    }
    @Override
    public void fillInsertContent(DataRuntime runtime, Run run, String dest, Collection list, ConfigStore configs, LinkedHashMap<String, Column> columns){
        if(null == list || list.isEmpty()){
            return;
        }
        StringBuilder builder = run.getBuilder();
        if(null == builder){
            builder = new StringBuilder();
            run.setBuilder(builder);
        }
        if(list instanceof DataSet){
            DataSet set = (DataSet) list;
            fillInsertContent(runtime, run, dest, set, configs, columns);
            return;
        }
        Object first = list.iterator().next();
        Map<String, Sequence> sequens = new HashMap<>();
        for(Column column:columns.values()){
            String key = column.getName();
            Object value = BeanUtil.getFieldValue(first, key);
            if(null != value) {
                if(null != value) {
                    if(value instanceof String) {
                        String str = (String) value;
                        if (str.toUpperCase().contains(".NEXTVAL")) {
                            if (str.startsWith("${") && str.endsWith("}")) {
                                str = str.substring(2, str.length() - 1);
                            }
                            sequens.put(key, new Sequence(str));
                        }
                    }else if(value instanceof Sequence){
                        Sequence sequence = (Sequence) value;
                        if (sequence.isFetchValueBeforeInsert()) {
                            createPrimaryValue(runtime, list, sequence);
                        } else {
                            sequens.put(key, sequence);
                        }
                    }
                }
            }
        }

        PrimaryGenerator generator = checkPrimaryGenerator(type(), dest.replace(getDelimiterFr(), "").replace(getDelimiterTo(), ""));
        LinkedHashMap<String, Column> pks = null;
        if(null != generator) {
            pks = EntityAdapterProxy.primaryKeys(first.getClass());
            columns.putAll(pks);
        }
        String select = insertsSelect(runtime, run, dest, list, configs, columns, sequens, generator, pks);
        Boolean override = null;
        if(null != configs){
            override = configs.override();
        }
        if(null == override) {
            builder.append("INSERT INTO ");
            SQLUtil.delimiter(builder, dest, getDelimiterFr(), getDelimiterTo()).append(" (");
            boolean start = true;
            for (Column column : columns.values()) {
                if (!start) {
                    builder.append(", ");
                }
                start = false;
                String key = column.getName();
                builder.append(key);
            }
            builder.append(") \n");
            builder.append(select);
        }else{
            //重复时 是否覆盖
            merge(builder, dest, configs, select, columns, pks);
        }
    }

    protected void merge(StringBuilder builder, String dest, ConfigStore configs, String select, LinkedHashMap<String, Column> columns, LinkedHashMap<String, Column> pks){
        List<String> bys = configs.overrideByColumns();
        if(null == bys){
            bys = new ArrayList<>();
        }
        if(bys.isEmpty()){
            bys = Column.names(pks);
        }
        builder.append("MERGE INTO ");
        SQLUtil.delimiter(builder, dest, getDelimiterFr(), getDelimiterTo());
        builder.append(" M\n");
        builder.append("USING (\n");
        builder.append(select);
        builder.append(") D ON(");
        builder.append(concatEqual("D", "M", "AND", bys)).append(")\n");
        //不存的正常插入
        builder.append("WHEN NOT MATCHED THEN \n");
        builder.append("INSERT(");
        builder.append(concat("M",",", Column.names(columns)));
        builder.append(")VALUES(");
        builder.append(concat("D",",", Column.names(columns)));
        builder.append(")\n");
        //如果需要覆盖
        if(configs.override()){
            builder.append("WHEN MATCHED THEN \n");
            List<String> cols = Column.names(columns);
            cols.removeAll(bys);//不更新主键
            builder.append("UPDATE SET ").append(concatEqual("M","D", ",", cols));
        }
    }
    protected String insertSelectHead(LinkedHashMap<String, Column> columns, Map<String, Sequence> sequens){
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT ");
        boolean start = true;
        for(Column column:columns.values()){
            String key = column.getName();
            Sequence seq = sequens.get(key);
            if(!start){
                builder.append(", ");
            }
            start = false;
            if(null != seq){
                builder.append(seq.sql());
            }else{
                builder.append("I.").append(key);
            }
            builder.append(" AS ").append(key);
        }
        builder.append("\nFROM( ");
        for(Sequence seq:sequens.values()){
            columns.remove(seq.sql().toUpperCase());
        }
        return builder.toString();
    }
    //批量插入select 部分
    protected String insertsSelect(DataRuntime runtime, Run run, String dest, DataSet set, ConfigStore configs, LinkedHashMap<String, Column> columns, Map<String, Sequence> sequens, PrimaryGenerator generator, LinkedHashMap<String, Column> pks){
        StringBuilder builder = new StringBuilder();
        builder.append(insertSelectHead(columns, sequens));
        int col = 0;
        for(DataRow row:set) {
            if(row.hasPrimaryKeys() && null != generator){
                generator.create(row, type(),dest.replace(getDelimiterFr(), "").replace(getDelimiterTo(), ""), pks,  null);
            }

            if(col > 0){
                builder.append("\n\tUNION ALL");
            }
            builder.append("\n\tSELECT ");
            builder.append(insertValue(runtime, run, row, true,true, true,false, columns));
            builder.append(" FROM DUAL ");
            col ++;
        }
        builder.append(") I ");
        return builder.toString();
    }
    protected String insertsSelect(DataRuntime runtime, Run run, String dest, Collection list, ConfigStore configs, LinkedHashMap<String, Column> columns, Map<String, Sequence> sequens, PrimaryGenerator generator, LinkedHashMap<String, Column> pks){
        StringBuilder builder = new StringBuilder();
        builder.append(insertSelectHead(columns, sequens));
        int col = 0;
        for(Object obj:list){
            boolean create = EntityAdapterProxy.createPrimaryValue(obj, pks);
            if(!create && null != generator){
                generator.create(obj, type(),dest.replace(getDelimiterFr(), "").replace(getDelimiterTo(), ""), pks,  null);
            }
            if(col > 0){
                builder.append("\n\tUNION ALL");
            }
            builder.append("\n\tSELECT ");
            builder.append(insertValue(runtime, run, obj, true,true, true,false, columns));
            builder.append(" FROM DUAL ");
            col ++;
        }
        builder.append(") I ");
        return builder.toString();
    }
    protected void createPrimaryValue(DataRuntime runtime, Collection list, Sequence sequence){
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT ").append(sequence.sql()).append(" AS ID FROM(\n");
        int size = list.size();
        for(int i=0; i<size; i++){
            builder.append("SELECT NULL FROM DUAL\n");
            if(i<size-1){
                builder.append("UNION ALL\n");
            }
        }
        builder.append(") M");
        JdbcTemplate jdbc = jdbc(runtime);
        List<Map<String,Object>> ids = jdbc.queryForList(builder.toString());
        int i=0;
        for(Object obj:list){
            Object value = ids.get(i++).get("ID");
            setPrimaryValue(obj, value);
        }
    }
}
